﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_StocksCordonInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddG_StocksCordonInfo(G_StocksCordonInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_StocksCordonInfo");
            strSql.Append("(");
            strSql.Append("GoodsId,");
            strSql.Append("CordonNum,");
            strSql.Append("Status,");
            strSql.Append("AddUser,");
            strSql.Append("AddTime,");
            strSql.Append("LastEditUser,");
            strSql.Append("LastEditTime");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@GoodsId,");
            strSql.Append("@CordonNum,");
            strSql.Append("@Status,");
            strSql.Append("@AddUser,");
            strSql.Append("@AddTime,");
            strSql.Append("@LastEditUser,");
            strSql.Append("@LastEditTime");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId },
                new SqlParameter("@CordonNum", SqlDbType.Int, 4) { Value = model.CordonNum },
                new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status },
                new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser },
                new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime },
                new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser },
                new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_StocksCordonInfo(int StocksCordonId)
        {
            string strSql = "DELETE FROM G_StocksCordonInfo WHERE StocksCordonId=@StocksCordonId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StocksCordonId", SqlDbType.Int, 4) { Value = StocksCordonId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditG_StocksCordonInfo(G_StocksCordonInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StocksCordonInfo SET ");
            strSql.Append(" GoodsId=@GoodsId,");
            strSql.Append(" CordonNum=@CordonNum,");
            strSql.Append(" LastEditUser=@LastEditUser,");
            strSql.Append(" LastEditTime=@LastEditTime");
            strSql.Append(" WHERE StocksCordonId=@StocksCordonId");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@StocksCordonId", SqlDbType.Int, 4) { Value = model.StocksCordonId },
                new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId },
                new SqlParameter("@CordonNum", SqlDbType.Int, 4) { Value = model.CordonNum },
                new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser },
                new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<G_StocksCordonInfo> GetG_StocksCordonInfoList(string strWhere)
        {
            List<G_StocksCordonInfo> list = new List<G_StocksCordonInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" StocksCordonId");
            strSql.Append(",GoodsId");
            strSql.Append(",CordonNum");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_StocksCordonInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StocksCordonId = r.GetOrdinal("StocksCordonId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int CordonNum = r.GetOrdinal("CordonNum");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_StocksCordonInfo model = null;
                while (r.Read())
                {
                    model = new G_StocksCordonInfo();
                    model.StocksCordonId = r.GetInt32(StocksCordonId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.CordonNum = r.GetInt32(CordonNum);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_StocksCordonInfo> GetG_StocksCordonInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_StocksCordonInfo> list = new List<G_StocksCordonInfo>();
            totalCount = GetG_StocksCordonInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY StocksCordonId DESC ) AS ORDERBYID ,
                    StocksCordonId ,
                    dbo.G_GoodsInfo.GoodsId ,
                    CordonNum ,
                    ColorName ,
                    TypeName ,
                    UnitValue ,
                    GoodsName ,
                    GoodsCode ,
                    Specs
          FROM      G_StocksCordonInfo WITH ( NOLOCK )
                    LEFT JOIN dbo.G_GoodsInfo WITH ( NOLOCK ) ON dbo.G_StocksCordonInfo.GoodsId = dbo.G_GoodsInfo.GoodsId
          WHERE     dbo.G_GoodsInfo.[Status] = 0 {0}
        ) AS T
WHERE   T.ORDERBYID BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StocksCordonId = r.GetOrdinal("StocksCordonId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int CordonNum = r.GetOrdinal("CordonNum");
                int ColorName = r.GetOrdinal("ColorName");
                int TypeName = r.GetOrdinal("TypeName");
                int GoodsName = r.GetOrdinal("GoodsName");
                int UnitValue = r.GetOrdinal("UnitValue");
                int GoodsCode = r.GetOrdinal("GoodsCode");
                int Specs = r.GetOrdinal("Specs");
                G_StocksCordonInfo model = null;
                while (r.Read())
                {
                    model = new G_StocksCordonInfo
                    {
                        StocksCordonId = r.GetInt32(StocksCordonId),
                        GoodsId = r.GetInt32(GoodsId),
                        CordonNum = r.GetInt32(CordonNum),
                        ColorName = r.GetString(ColorName),
                        TypeName = r.GetString(TypeName),
                        GoodsName = r.GetString(GoodsName),
                        UnitValue = r.GetInt32(UnitValue),
                        GoodsCode = r.GetString(GoodsCode),
                        Specs = r.GetString(Specs)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_StocksCordonInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"SELECT  COUNT(*)
FROM    G_StocksCordonInfo WITH ( NOLOCK )
        LEFT JOIN dbo.G_GoodsInfo WITH ( NOLOCK ) ON dbo.G_StocksCordonInfo.GoodsId = dbo.G_GoodsInfo.GoodsId
WHERE   dbo.G_GoodsInfo.[Status] = 0 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public int GetStocksCordonInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_StocksCordonInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_StocksCordonInfo GetG_StocksCordonInfoModel(int stocksCordonId)
        {
            G_StocksCordonInfo model = null;
            string strSql = @"
SELECT    
                    StocksCordonId ,
                    dbo.G_GoodsInfo.GoodsId ,
                    CordonNum ,
                    ColorName ,
                    TypeName ,
                    UnitValue ,
                    GoodsName ,
                    GoodsCode ,
                    Specs
          FROM      G_StocksCordonInfo WITH ( NOLOCK )
                    LEFT JOIN dbo.G_GoodsInfo WITH ( NOLOCK ) ON dbo.G_StocksCordonInfo.GoodsId = dbo.G_GoodsInfo.GoodsId 
                    WHERE StocksCordonId=@StocksCordonId";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@StocksCordonId", SqlDbType.Int, 4) { Value = stocksCordonId }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql, pList.ToArray()))
            {
                int StocksCordonId = r.GetOrdinal("StocksCordonId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int CordonNum = r.GetOrdinal("CordonNum");
                int ColorName = r.GetOrdinal("ColorName");
                int TypeName = r.GetOrdinal("TypeName");
                int GoodsName = r.GetOrdinal("GoodsName");
                int UnitValue = r.GetOrdinal("UnitValue");
                int GoodsCode = r.GetOrdinal("GoodsCode");
                int Specs = r.GetOrdinal("Specs");

                if (r.Read())
                {
                    model = new G_StocksCordonInfo
                    {
                        StocksCordonId = r.GetInt32(StocksCordonId),
                        GoodsId = r.GetInt32(GoodsId),
                        CordonNum = r.GetInt32(CordonNum),
                        ColorName = r.GetString(ColorName),
                        TypeName = r.GetString(TypeName),
                        GoodsName = r.GetString(GoodsName),
                        UnitValue = r.GetInt32(UnitValue),
                        GoodsCode = r.GetString(GoodsCode),
                        Specs = r.GetString(Specs)
                    };
                }
                r.Close();
            }
            return model;
        }


        public List<G_StocksCordonInfo> GetG_StocksCordonInfoList()
        {
            List<G_StocksCordonInfo> list = new List<G_StocksCordonInfo>();
            string strSql = @"
 SELECT dbo.G_StocksCordonInfo.GoodsId ,
        Num,
        CordonNum
 FROM   dbo.G_StocksCordonInfo WITH ( NOLOCK )
        LEFT JOIN dbo.G_StocksTotalInfo WITH ( NOLOCK ) ON dbo.G_StocksCordonInfo.GoodsId = dbo.G_StocksTotalInfo.GoodsId
 WHERE  Num < CordonNum
 ";

            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql))
            {

                int GoodsId = r.GetOrdinal("GoodsId");
                int Num = r.GetOrdinal("Num");
                int CordonNum = r.GetOrdinal("CordonNum");
                G_StocksCordonInfo model = null;
                while (r.Read())
                {
                    model = new G_StocksCordonInfo
                    {
                        GoodsId = r.GetInt32(GoodsId),
                        Num = r.GetDecimal(Num),
                        CordonNum = r.GetInt32(CordonNum)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }
    }
}
